const database = require("../databaseConfig/index");
const dayjs = require("dayjs");

// 通用封装：将 database.query 转为 Promise 风格
const query = (sql, params = [], conn = database) => {
  return new Promise((resolve, reject) => {
    conn.query(sql, params, (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
};

// 封装事务处理函数
const withTransaction = async (taskFn) => {
  const conn = await new Promise((resolve, reject) => {
    database.getConnection((err, connection) => {
      if (err) return reject(err);
      resolve(connection);
    });
  });

  try {
    await query("START TRANSACTION", [], conn);
    const result = await taskFn(conn);
    await query("COMMIT", [], conn);
    conn.release();
    return result;
  } catch (error) {
    await query("ROLLBACK", [], conn);
    conn.release();
    throw error;
  }
};

// 创建或更新公司
exports.createOrUpdateCompany = async ({
  companyId,
  name,
  industry,
  address,
  userId,
  logo,
}) => {
  if (companyId) {
    const updateSQL = `
      UPDATE company 
      SET FName = ?, FIndustry = ?, FAddress = ?, FLogo = ?, FUpdateTime = NOW()
      WHERE Fid = ?
    `;
    await query(updateSQL, [name, industry, address, logo, companyId]);
    return { result: "success" };
  }

  // 创建公司并绑定用户 — 事务处理
  return withTransaction(async (conn) => {
    const insertSQL = `
      INSERT INTO company (FName, FIndustry, FAddress, FLogo, FCreateTime, FUpdateTime)
      VALUES (?, ?, ?, ?, NOW(), NOW())
    `;
    const result = await query(
      insertSQL,
      [name, industry, address, logo || null],
      conn
    );
    const newCompanyId = result.insertId;

    if (!newCompanyId) {
      throw new Error("公司创建失败，未返回 ID");
    }

    if (userId) {
      const updateUserSQL = `UPDATE user SET FCompanyId = ? WHERE Fid = ?`;
      await query(updateUserSQL, [newCompanyId, userId], conn);
    }

    return {
      result: "success",
      companyId: newCompanyId,
    };
  });
};

// 获取公司信息
exports.getCompanyInfo = async (id) => {
  const sql = `
    SELECT 
      Fid as id, 
      FLogo as logo, 
      FName as name, 
      FIndustry as industry, 
      FAddress as address
    FROM company 
    WHERE Fid = ?
  `;
  const result = await query(sql, [id]);
  return result[0] || null;
};

// 签到功能
exports.checkin = async ({ companyId, userId, date }) => {
  const checkSQL = `
    SELECT 1 FROM checkin 
    WHERE FCompanyId = ? AND FUserId = ? AND DATE(FCheckinTime) = ?
    LIMIT 1
  `;
  const existing = await query(checkSQL, [companyId, userId, date]);

  if (existing.length > 0) {
    throw new Error("今日已签到");
  }

  const insertSQL = `
    INSERT INTO checkin (FUserId, FCompanyId, FCheckinTime)
    VALUES (?, ?, NOW())
  `;
  await query(insertSQL, [userId, companyId]);

  const infoSQL = `
    SELECT 
      c.FName as name,
      u.FName as username
    FROM company c, user u
    WHERE c.Fid = ? AND u.Fid = ?
  `;
  const info = await query(infoSQL, [companyId, userId]);

  return {
    success: true,
    date: dayjs().format("YYYY-MM"),
    name: info[0]?.name || "",
    username: info[0]?.username || "",
  };
};

// 获取签到记录
exports.getCheckinList = async ({ companyId, userId, date }) => {
  const where = [];
  const params = [];

  if (companyId) {
    where.push("c.Fid = ?");
    params.push(companyId);
  }
  if (userId) {
    where.push("u.Fid = ?");
    params.push(userId);
  }
  if (date) {
    where.push("DATE(ch.FCheckinTime) = ?");
    params.push(date);
  }

  const whereClause = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const sql = `
    SELECT 
      ch.FID as id,
      u.Fid as userId,
      u.FName as username,
      c.Fid as companyId,
      c.FName as companyName,
      ch.FCheckinTime as checkinTime
    FROM checkin ch
    JOIN user u ON ch.FUserId = u.Fid
    JOIN company c ON ch.FCompanyId = c.Fid
    ${whereClause}
    ORDER BY ch.FCheckinTime DESC
  `;

  return await query(sql, params);
};

// 获取公司列表
exports.getCompanyList = async ({ name }) => {
  const where = [];
  const params = [];

  if (name) {
    where.push("FName LIKE ?");
    params.push(`%${name}%`);
  }

  const whereClause = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const sql = `
    SELECT 
      Fid as id,
      FName as name,
      FAddress as address,
      FIndustry as industry
    FROM company
    ${whereClause}
    ORDER BY FName
  `;

  return await query(sql, params);
};
